/*
 * ConexionAvw.java
 *
 * Created on 10 de abril de 2007, 16:52
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package bd;
import bd.BoundParam;
import excepciones.AvwException;

import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.math.BigDecimal;
import java.net.URL;
import java.sql.Array;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.NClob;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.Ref;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.RowId;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import java.util.Locale;
import java.util.logging.Level;
import java.util.Properties;

import java.util.logging.Logger;

import javax.swing.JOptionPane;

import util.Utiles;
/**
 *
 * @author Administrador
 */
public class ConexionAvw {
    
    private static Connection conn;
    private String driver;
    private String url;
    private String user;
    private String password;
    static final Logger logger = Utiles.iniciarLogger("ConexionAvw", true);

    /** Creates a new instance of ConexionAvw */
    public ConexionAvw() {
        loadConfiguration();
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException ex) {
            logger.log(Level.SEVERE, ex.getMessage(), ex);
        }
    }
    
    private void loadConfiguration()  {
        Properties p = new Properties();
        try {
            InputStream file =
                this.getClass().getClassLoader().getResourceAsStream("configuracion.properties");
            p.load(file);
            this.driver = p.getProperty("driver");
            this.user = p.getProperty("usuario", "");
            this.password = p.getProperty("password", "");
            this.url = p.getProperty("url");
            Locale.setDefault(Locale.ENGLISH);
            
        } catch (IOException ex) {
            logger.log(Level.SEVERE, ex.getMessage(), ex);
        }
    }
    
    public void abrirConexion() throws SQLException {
        

        conn = DriverManager.getConnection(url, user, password);
    }
    
    public static void cerrarConexion() throws AvwException {
        
        try {
            conn.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, ex.getMessage(), ex);
            throw new AvwException(ex);
        }
        
    }
    
    public void startTransaction() throws SQLException {
        conn.setAutoCommit(false);
    }
    
    public void commitTransaction() throws SQLException {
        conn.commit();
    }
    
    public void rollbackTransaction() throws SQLException {
        conn.rollback();
    }
    
    /**
     * Solo se debe utilizar para
     *
     *
     */
    private ResultSet getResultSet(StringBuilder sql) throws SQLException {
        PreparedStatement pstmt = null;
        pstmt = conn.prepareStatement(sql.toString());
        pstmt.executeQuery();
        return pstmt.getResultSet();
    }

    /**
     * @param sql     String
     * @param boundparams     ArrayList
     * @throws Exception
     * @return ResultSet
     */
    public  ResultSet getPreparedStatementResultSet(String sql,
        ArrayList boundparams) throws SQLException {
        
        PreparedStatement pstmt = null;
        ResultSet r = null;
        pstmt = conn.prepareStatement(sql);
        logger.info(sql);
        
        setPreParams(pstmt, boundparams);// Seteo los parametros
        r = pstmt.executeQuery();
        
        return r;
    }
    
    public  ResultSet getPreparedStatementResultSet(StringBuilder sql,
        ArrayList boundparams) throws SQLException{
        return getPreparedStatementResultSet(sql.toString(),boundparams);
    }
    
    public  void executePreparedStatement(String sql,
        ArrayList boundparams) throws SQLException {
        PreparedStatement pstmt = null;
        pstmt = conn.prepareStatement(sql);
        setPreParams(pstmt, boundparams);// Seteo los parametros
        logger.info(sql.toString());
        pstmt.executeQuery();
    }
    
    /**
    *
    */
    public  ResultSet getCallableStatement(String sql,
        ArrayList<ParamCallable> boundparams,int nroParametroRetorno) throws SQLException {
        CallableStatement callStmt = null;
        callStmt = conn.prepareCall(sql);
        //////////////////////////////////
           for(ParamCallable param : boundparams )      
            {
                if(param.type == ParamCallable.IN ){
                    callStmt.setObject(param.index, param.value,param.sqlType);
                }else{
                    callStmt.registerOutParameter(param.index,param.sqlType);
                }//FIN IF TYPE
            }//fin for
        ////////////////////////////////////////
        callStmt.execute();
        return (ResultSet) callStmt.getObject(nroParametroRetorno);
    }

    public  int getIntCallableStatement(String sql,ArrayList<ParamCallable> boundparams,int nroParametroRetorno) throws SQLException {
        CallableStatement callStmt = null;
        callStmt = conn.prepareCall(sql);
        //////////////////////////////////
           for(ParamCallable param : boundparams )      
            {
                if(param.type == ParamCallable.IN ){
                    callStmt.setObject(param.index, param.value,param.sqlType);
                }else{
                    callStmt.registerOutParameter(param.index,param.sqlType);
                }//FIN IF TYPE
            }//fin for
        ////////////////////////////////////////
        callStmt.execute();
        int retorna =(int) callStmt.getInt(nroParametroRetorno);
        return retorna;
    }


     public  void executePreparedStatement(StringBuilder sql,
        ArrayList boundparams) throws SQLException {
        executePreparedStatement(sql.toString(),boundparams); 
    }
    
    private void setPreParams(PreparedStatement ps,
        List plist)  throws SQLException {
        
        List boundParams = plist;
        if (ps == null || boundParams == null || boundParams.size() == 0)
            return;
        
        BoundParam param;
        
        for (Iterator itr = boundParams.iterator(); itr.hasNext();) {
            param = (BoundParam) itr.next();
            if (param == null)
                continue;
            if (param.sqlType == java.sql.Types.OTHER) {
                ps.setObject(param.index, param.value);
            } else {
                logger.info("Parametro index : " + param.index +" valor: "+ param.value );
                ps.setObject(param.index, param.value, param.sqlType,  param.scale);
            }
        }
    }

    private void setParamCallable(CallableStatement callStmt,ArrayList<ParamCallable> paramCallable) throws SQLException {
           for(ParamCallable param : paramCallable )      
            {
                if(param.type == ParamCallable.IN ){
                    callStmt.setObject(param.index, param.value,param.sqlType);
                }else{
                    callStmt.registerOutParameter(param.index,param.sqlType);
                }//FIN IF TYPE
            }//fin for

        }
 }
    

